Consultoría de Bancos
Bases de Datos………………………………………………………………………………………………………. 2Enunciado………………………………………………………………………………………………………….2**Diseño conceptual…………………………………………………………………………………………….. 4**Diseño lógico……………………………………………………………………………………………………..4**Entidades……………………………………………………………………………………………………… 4Relaciones N:M o N:M:P………………………………………………………………………………….5Normalización…………………………………………………………………………………………………5Diseño en MySQL Workbench…………………………………………………………………………….6Consultas……………………………………………………………………………………………………… 9Microsoft Access………………………………………………………………………………………………14Importación de los datos……………………………………………………………………………….. 14Subformularios…………………………………………………………………………………………….. 17MongoDB………………………………………………………………………………………………………… 20Paso de tablas a NoSQL………………………………………………………………………………..20Banco……………………………………………………………………………………………………. 21Pais………………………………………………………………………………………………………..21Cuenta……………………………………………………………………………………………………21Cliente…………………………………………………………………………………………………… 22Empleado………………………………………………………………………………………………. 23Consultas……………………………………………………………………………………………………. 23Página web…………………………………………………………………………………………………………… 34HTML………………………………………………………………………………………………………………..35CSS………………………………………………………………………………………………………………….36JavaScript mouseover…………………………………………………………………………………………39Párrafo…………………………………………………………………………………………………………39Imágenes……………………………………………………………………………………………………..39
JS sugerencias…………………………………………………………………………………………………..41Posible mejora………………………………………………………………………………………………43Consultas…………………………………………………………………………………………………………. 43Consulta_nombres……………………………………………………………………………………….. 43Consulta_transacciones…………………………………………………………………………………44Insertar, eliminar datos…………………………………………………………………………………..44Problema surgido de array de arrays………………………………………………………….44Consulta SQL complicada………………………………………………………………………………46
BankingConsulting quiere una base de datos en la que almacena la información relativa a bancos.
De un país se desea guardar su Nombre y Población y un ID.
De los bancos su sede, nombre e ID.
De las divisas la tasa de cambio e ID.
De las cuentas sus balances, tipo (depósito o cuenta corriente) e iD.
De las transacciones la fecha, cantidad, descripción e ID.
De los préstamos la cantidad, fecha inicio, fecha vencimiento e ID.
De los empleados su nombre, apellido e ID.
Subrayado indica que dicho atributo(s) es(son) “Primary Key”
10.Miembro_Equipo(ID_Miembro_Equipo, ID_Empleado, ID_Supervisor)
En Divisa el código podría haber sido “Primary Key” y en Supervisor y Miembro_Equipo el ID_Empleado podría haber funcionado como “Primary Key”, sin embargo, prefiero añadir nuevos IDs por guardar la consistencia con el resto de entidades.
Vamos a estudiar si el esquema lógico está en 3FN. Primero, usando la definición de clase de 1FN:
“Una tabla relacional R está en primera forma normal (1FN) si NO contiene campos multivaluados (cada atributo 1 valor).”
Como nuestro diseño no tiene campo multievaludados, concluimos que está en 1FN.
Para ver si está en 2FN es necesario que esté en 1FN y, además, que no exista dependencia transitiva entre campos no principales y claves candidatas.
Luego todas las tablas están en 2FN
Y no hay dependencias transitivas, luego, como es 2FN, también es 3FN.
Primero he generado los archivos SQL:
He usado la herramienta de “reverse engineering” para hacer el diseño lógico en MySQL Workbench.
Obtenemos el diagrama lógico
Archivo del modelo de MySQL workbench:
Las consultas están en el archivo queries.sql
SELECT * FROM Cliente WHERE Fecha_Nacimiento > ‘1960-12-31’;
SELECT * FROM Cliente WHERE Nombre LIKE ‘D%’;
SELECT Nombre
FROM Cliente WHERE ID_Cliente IN
(SELECT ID_Cliente FROM Cliente_Cuenta GROUP BY ID_Cliente
HAVING COUNT(ID_Cuenta) > 1);
SELECT ID_Cliente
FROM Cliente_Banco WHERE ID_Banco IN (SELECT ID_Banco FROM Banco WHERE Nombre IN (‘Banco Bilbao’, ‘Deutsche Bank’));
SELECT ID_Cliente, Nombre
FROM Cliente WHERE ID_Cliente IN
(SELECT ID_Cliente FROM Cuenta
INNER JOIN Cliente_Cuenta ON Cuenta.ID_Cuenta = Cliente_Cuenta.ID_Cuenta GROUP BY ID_Cliente HAVING SUM(Balance) < 0);
SELECT ID_Transaccion FROM Transaccion
INNER JOIN Cuenta ON Transaccion.ID_Cuenta = Cuenta.ID_Cuenta WHERE Transaccion.ID_Divisa != Cuenta.ID_Divisa;
SELECT Transaccion.Monto * D1.Tasa_cambio*1/D2.Tasa_cambio
FROM Transaccion
– Join de la tabla de transacciones con la tabla de cuentas
INNER JOIN Cuenta ON Transaccion.ID_Cuenta = Cuenta.ID_Cuenta
– Hago el JOIN para obtener Tasa de cambio de la moneda de la transacción INNER JOIN Divisa as D1 ON Transaccion.ID_Divisa = D1.ID_Divisa
– Hago el JOIN para obtener Tasa de cambio de la moneda de la cuenta INNER JOIN Divisa as D2 ON Cuenta.ID_Divisa = D2.ID_Divisa
WHERE Transaccion.ID_Divisa != Cuenta.ID_Divisa;
SELECT Nombre FROM Cliente WHERE ID_Cliente IN
(SELECT ID_Cliente FROM Cliente_Cuenta WHERE ID_Cuenta IN (SELECT ID_Cuenta FROM Cuenta WHERE ID_Divisa != 1));
GROUP BY ID_Pais HAVING COUNT(ID_Banco) <= 2;
SELECT * FROM Divisa
WHERE ID_Divisa NOT IN (SELECT ID_Divisa FROM Cuenta);
SELECT ID_Divisa FROM Pais
GROUP BY ID_Divisa HAVING COUNT(ID_Pais) < 2;
AÑADIR FOTO
SELECT * FROM Prestamo WHERE DATEDIFF(Fecha_fin, Fecha_inicio) >
365*10;
SELECT ID_Cliente FROM Prestamo
WHERE DATEDIFF(Fecha_fin, Fecha_inicio) > 365*10;
SELECT Cliente_Prestamo_Banco.ID_Cliente FROM Cliente_Prestamo_Banco
INNER JOIN Cliente_Banco
ON Cliente_Prestamo_Banco.ID_Cliente = Cliente_Banco.ID_Cliente
WHERE Cliente_Prestamo_Banco.ID_Banco != Cliente_Banco.ID_Banco;
SELECT Transaccion.Monto*Divisa.Tasa_cambio, Transaccion.* FROM
Transaccion
INNER JOIN Divisa ON Transaccion.ID_Divisa = Divisa.ID_Divisa;
Usado en Problema surgido de array de arrays
SELECT ID_Cuenta FROM Cliente_Cuenta WHERE ID_Cuenta IN
(SELECT ID_Cuenta
FROM Cliente_Cuenta
GROUP BY ID_Cuenta
HAVING COUNT(*) = 1)
AND ID_Cliente = 1
Vídeos útiles:
Exportamos los datos de phpmyadmin seleccionando la tabla que queramos exportar y los datos de la tabla que deseemos y haciendo click sobre export. Además escogemos el formato “CSV for MS Excel”.
Luego en la foto pinchamos en Archivo de texto
En examinar escogemos el archivo csv que hemos exportamos y en la imagen siguiente vemos dónde escogemos la tabla donde queremos importar los datos pulsamos aceptar y acabamos.
Son útiles para mostrar datos de tablas con relaciones N:M. Por ejemplo, vamos a crear un formulario para mostrar los bancos que operan en un determinado país.
Primero pinchamos en Asistente para formularios.
Luego elegimos los campos que queremos que se muestren de cada tabla.
Por último, elegimos agrupar los bancos por país.
Pinchamos en ver para ir a vista de diseño. Pinchamos sobre botones. Elegimos un botón para navegar a anterior y siguiente registro.
Nos queda el formulario con subformulario tal que así:
Primero hay que adaptar la parte del modelo entidad relación a colecciones (NoSQL). En la siguiente imagen se puede ver en verde la parte del modelo que voy a adaptar.
Colección con Nombre, Sede, ID (los genera automáticamente Mongo DB) y para conservar la relación entre País y Banco creo una colección con un array con los IDs de Pais.
Nombre, Poblacion y colección llamada Divisa con Código y Tasa de Cambio.
Divisa como subcolección y relación con Cliente mediante array de códigos.
Para adaptar la relación jerárquica vamos a poner un campo Supervisor_ID, que será null para los supervisores (no son supervisados por nadie) pero cuando sea un miembro de un equipo (no supervisor) tendrá el ID del supervisor asignado.
Los archivos JSON anteriores se encuentran en github:
Las consultas siguientes se encuentran en el siguiente fichero consultas.txt
db.Pais.find({ “Poblacion”: { $gt: 50 } })
{
$project: {
Nombre: 1,
Sede: 1,
NumPaises: { \(size: "\)Pais” }
}
}, {
$match: {
NumPaises: { $gt: 5 }
}
}
]) ó
db.Banco.find({
$where: “this.Pais.length > 5” })
CORREGIR
db.Cuenta.find({ “Tipo”: “Ahorros”, “Balance”: { $lt: 0 } })
db.Banco.aggregate([
{ $project: { “Nombre”: 1, “Numero_Paises”: { \(size: "\)Pais” } } }, { $sort: { “Numero_Paises”: -1 } },
{ $limit: 3 }
])
{
$project: {
“Nombre”: 1,
“Apellido”: 1,
“Fecha_nacimiento”: { \(toDate: "\)Fecha_nacimiento” },
“Edad”: {
$floor: {
$divide: [
{ $subtract: [new Date(), { \(toDate: "\)Fecha_nacimiento” }] }, 31536000000 // Milisegundos en un año
]
}
}
}
},
{ $sort: { “Edad”: 1 } },
{ $limit: 1 }
])
db.Cuenta.aggregate([
{ $match: { “Divisa.Codigo”: “EUR” } }, {
$group: {
_id: “$ID_Banco”,
Saldo_Total: { \(sum:
"\)Balance” }
}
}
])
db.Empleado.aggregate([
{
$match: { “ID_Banco”: 1 }
},
{
$project: {
“Fecha_contratacion”: { \(toDate: "\)Fecha_contratacion” }, “Edad”: {
$floor: {
$divide: [
{ $subtract: [new Date(), { \(toDate: "\)Fecha_contratacion” }] }, 31536000000 // Milisegundos en un año
]
}
}
}
}, {
$group: {
_id: null,
EdadPromedio: { \(avg: "\)Edad” } }
}
])
db.Cliente.find({
“ID_Cliente”: { $nin: db.Cuenta.distinct(“ID_Cliente”) } })
Bankconsulting nos ha pedido diseñar una página web en la que puedan describir la empresa y su filosofía. Además, quieren poder filtrar los nombres de los clientes por su nombre y/o apellidos y ver la consultas más nuevas de una fecha dada y una divisa seleccionada.
Se puede visitar la web en github: https://danmorper.github.io/ pero no tiene la parte de servidor.
Primero creamos el archivo index.html. Luce tal que así:
He usado bootstrap5 para estructurar la página.
Se compone de una barra de navegación fija en la parte de arriba de la pantalla y de una parte inferior que cambia según donde pulses en la barra de navegación.
Si pinchas en “sobre nosotros” ves una breve descripción de lo que es BankConsulting (por defecto cuando abres la web).
Si pinchas sobre “Consulta nombres” te aparece un formulario en el que escribes nombre y/o apellidos de el/los cliente/s que quieres filtrar. Al pinchar sobre “Enviar” te aparecen los clientes que cuadran con la búsqueda en la tabla inferior.
Si pinchas sobre “Consulta transacciones” te aparecen 3 columnas.
Barra de Navegación de bootstrap5:
Contenido de las diferentes secciones de la barra de navegación:
La web tiene un footer con el contacto
En el head del html hemos añadido la hoja de estilos de bootstrap y una creada por mí, style.css
fuente: https://developer.mozilla.org/en-US/docs/Web/CSS/flex-direction*
Quiero que cuando pase el ratón por encima de:
Seleccionamos todos los párrafos que tienen la clase “cambiar” (los tienen todos, lo he puesto yo). Lo guardamos en variable parrafos.
con forEach hacemos dos event listener:
Sigue el mismo principio, pero con nuevaClase2 y se seleccionan las imágenes.
Hay un input text para escribir título de la sugerencia y textarea para escribir la sugerencia. Al pinchar sobre “Finalizar” se añade lo que se haya escrito después de “Sugerencias realizadas”. El título se mostrará pero la sugerencia estará oculta.
Cuando pincho sobre botón primero veo si no se ha añadido nada como título o sugerencia, en caso afirmativo saco alerta y NO CAMBIO num_sugerencias.
Si tengo titulo Y sugerencia, creo un elemento div que va a tener como hijos al título, con etiqueta h3, y texto de la sugerencia, con etiqueta p (que lo oculto al final).
Posteriormente, dejo en blanco el input y el textarea.
Para mostrarlo añadí una función aparece un observador(?) de eventos “onclick” como se ve en la siguiente foto:
Defino la función aparece que recibe el evento. uso .target para obtener el elemento que “sufre(?)” el evento, en este caso el h3. Tomo su id y lo guardo en la variable texto.
La id del h3 y del texto contienen el mismo número luego puedo acceder al texto que corresponde al h3 sobre el que he pinchado fácilmente.
Si está en style.display tiene el valor “block”, entonces se está mostrando, luego lo oculto dándole el valor “none”. En caso contrario,es decir, tiene el valor “none”, significa que no se está mostrando, luego le doy valor “block” para que sí se muestre.
Decidí hacerlo con *En esta sección no he añadido lo de cambiar la clase css al pasar el ratón por encima el ratón para no complicar más la cosa.
Hacer una base de datos en la que guarde las sugerencias para que cuando se cierre el navegador no se pierdan.
Además, añadir la funcionalidad de eliminar sugerencias.
Introduces nombre y/o apellidos y en la tabla azul te salen los registros que concuerden.
Tomo el formulario en la variable formulario y espero que suceda el evento “submit” para aplicar una función. Primero, uso preventDefault para evitar que el navegador me lleve al documento php. Luego, tomo los datos del formulario con new FormData (en consola se puede ver las propiedades de dicho objeto).
Hago una consulta mediante el método “POST” a consulta_nombres.php (el fetch).
El php recibe nombre y apellido y lo guarda en variables, establece la conexión mediante new PDO. Si el intento de conexión falla se devuelve “Error: (mensaje de error)”.
Hago la consulta preparada (uso :algo y bindParam en vez de poner directamente $algo en la consulta). fetchAll se usa para pasar de un array con subarrays (cada fila de datos de la consulta es un subarray) a un array asociativo.
ejemplo array predeterminado
ejemplo array asociativo
eb.
Hago json_encode y el echo.
Vuelvo al JS.
Si la respuesta es vacía se colapsan las columnas y se hace sólo una fila con el mensaje “No se encontraron resultados”
De qué trata explicado en Página web.
Procedemos de manera parecida a consulta_nombres co el addEventListener. Tomamos en divisaSeleccionada todas las divisas que han sido seleccionadas. divisaSeleccionada es un nodeList, que aunque es un objeto parecido a un Array no lo es, así que lo convertimos en un array con Array.fromy lo guardamos en divisaSeleccionada2.
La consulta que vamos a hacer es mediante el método get. Sería más apropiado hacerlo mediante el método post, ya que tenemos una array, pero vamos a tomarlo como un reto. Para solucionar el problema iteramos en el array y hacemos varias consultas get invidualmente y añadimos a la tabla para visualizar datos. Borramos la tabla cada vez que le damos al botón del formulario.
El php es una consulta get estándar salvo porque revisa que si no hay divisas seleccionadas hace la consulta teniendo en cuenta sólo la fecha, es decir, es como si hubiera seleccionado todas las divisas.
Voy a insertar clientes. Para ello inserto Nombre, Apellido y Fecha de nacimiento, además pongo ID_Cuenta que tiene, si no añado ningún ID_Cliente no se le asocia cuenta. La cuenta debe existir de antes.
Elimino clientes por el ID, si la cuenta que tenía asociada, se queda sin ningún cliente, elimino también la cuenta.
Problema surgido de array de arrays Tras la siguiente consulta (consulta16):
$resultado queda como una array de arrays, como se puede ver con un vardump:
(este vardump ha sido en un prueba.php y con unos datos que he añadido de prueba)
Lo he solucionado accediendo al elemento (INT) mediante la key del array “ID_Cuenta”.